import pandas as pd
import openpyxl
import numpy as np


def excel_to_dict(formal_file_path,test_file_path):
    """
    读取Excel前3行并生成指定格式的字典

    参数:
    file_path: Excel文件路径

    返回:
    指定格式的字典
    """
    try:
        # 读取Excel前3行
        df_formal = pd.read_excel(formal_file_path, nrows=3)
        df_test = pd.read_excel(test_file_path, nrows=3)

        # 初始化结果字典
        formal_result_dict = {}
        test_result_dict = {}


        # 遍历每一列(正式数据)
        for column in df_formal.columns:
            # 获取前两行的数据
            first_row = df_formal.iloc[0][column]
            second_row = df_formal.iloc[1][column]

            # 获取第二行的数据并判断是否为空
            third_value = 0 if pd.isna(second_row) or second_row == "" else 1

            # 构建该字段的数据列表
            formal_result_dict[column] = [
                str(first_row) if not pd.isna(first_row) else "",
                str(second_row) if not pd.isna(second_row) else "",
                third_value
            ]

        # 遍历每一列(测试数据)
        for column in df_test.columns:
            # 获取前两行的数据
            first_row = df_test.iloc[0][column]
            second_row = df_test.iloc[1][column]

            # 获取第二行的数据并判断是否为空
            third_value = 0 if pd.isna(second_row) or second_row == "" else 1

            # 构建该字段的数据列表
            test_result_dict[column] = [
                str(first_row) if not pd.isna(first_row) else "",
                str(second_row) if not pd.isna(second_row) else "",
                third_value
            ]

        return formal_result_dict, test_result_dict

    except Exception as e:
        print(f"处理文件时出错: {e}")
        return None

def compare_excel_dicts(test_dict, formal_dict):
    """
    对比两个Excel数据字典的差异

    参数:
    test_dict: 测试数据字典
    formal_dict: 正式数据字典

    返回:
    对比结果字典
    """

    # 获取两个字典的键集合
    test_keys = set(test_dict.keys())
    formal_keys = set(formal_dict.keys())

    # 1. 比较键的数量和名称是否一致
    # 共同的键
    common_keys = test_keys & formal_keys
    # 只在测试数据中的键
    only_in_test = test_keys - formal_keys
    # 只在正式数据中的键
    only_in_formal = formal_keys - test_keys

    # 2. 比较相同键对应的第三个值是否一致
    third_value_diff = {}
    for key in common_keys:
        # 测试数据的第二个值
        test_third = test_dict[key][1]
        # 正式数据的第二个值
        formal_third = formal_dict[key][1]

        if test_third != formal_third:
            third_value_diff[key] = {
                'test': test_third,
                'formal': formal_third
            }

    # 构建结果
    result = {
        'key_comparison': {
            'test_key_count': len(test_keys),
            'formal_key_count': len(formal_keys),
            'common_key_count': len(common_keys),
            'only_in_test_count': len(only_in_test),
            'only_in_formal_count': len(only_in_formal),
            'only_in_test': sorted(list(only_in_test)),
            'only_in_formal': sorted(list(only_in_formal)),
            'is_keys_identical': len(only_in_test) == 0 and len(only_in_formal) == 0
        },
        'third_value_comparison': {
            'total_common_keys': len(common_keys),
            'different_third_values_count': len(third_value_diff),
            'different_third_values': third_value_diff,
            'is_third_values_identical': len(third_value_diff) == 0
        },
        'overall_identical': (len(only_in_test) == 0 and
                              len(only_in_formal) == 0 and
                              len(third_value_diff) == 0)
    }

    return result


def print_comparison_result(result):
    """
    打印对比结果
    """
    print("=" * 60)
    print("Excel数据字典对比结果")
    print("=" * 60)

    # 键的比较结果
    key_comp = result['key_comparison']
    print("\n1. 键的比较:")
    print(f"   测试数据键数量: {key_comp['test_key_count']}")
    print(f"   正式数据键数量: {key_comp['formal_key_count']}")
    print(f"   共同键数量: {key_comp['common_key_count']}")
    print(f"   只在测试数据中的键数量: {key_comp['only_in_test_count']}")
    print(f"   只在正式数据中的键数量: {key_comp['only_in_formal_count']}")
    print(f"   键是否完全一致: {'是' if key_comp['is_keys_identical'] else '否'}")

    if key_comp['only_in_test_count'] > 0:
        print(f"\n   只在测试数据中的键:")
        for key in key_comp['only_in_test']:
            print(f"     - {key}")

    if key_comp['only_in_formal_count'] > 0:
        print(f"\n   只在正式数据中的键:")
        for key in key_comp['only_in_formal']:
            print(f"     - {key}")

    # 第三个值的比较结果
    third_comp = result['third_value_comparison']
    print(f"\n2. 第二个值的比较:")
    print(f"   共同键总数: {third_comp['total_common_keys']}")
    print(f"   第二个值不同的键数量: {third_comp['different_third_values_count']}")
    print(f"   第二个值是否完全一致: {'是' if third_comp['is_third_values_identical'] else '否'}")

    if third_comp['different_third_values_count'] > 0:
        print(f"\n   第二个值不同的键:")
        for key, values in third_comp['different_third_values'].items():
            print(f"     - {key}: 测试数据={values['test']}, 正式数据={values['formal']}")

    # 总体结果
    print(f"\n3. 总体结果:")
    print(f"   两个字典是否完全一致: {'是' if result['overall_identical'] else '否'}")
    print("=" * 60)

# 使用示例
if __name__ == "__main__":
    formal_file_path = r"D:\字段测试20251015\BI订单明细表_浪起科技N2025910XET12按引出模版.xlsx"
    test_file_path = r"D:\字段测试20251015\BI订单明细表_测试1002按引出模版引出425任务.xlsx"

    formal_result,test_result = excel_to_dict(formal_file_path,test_file_path)

    if formal_result:
        #打印结果
        print("{")
        for i, (key, value) in enumerate(formal_result.items()):
            if i < len(formal_result) - 1:
                print(f'    "{key}":{value},')
            else:
                print(f'    "{key}":{value}')
        print("}")

        # 也可以直接使用result字典
        print(f"总共{i+1}列")
        print("\nformal可以直接使用的字典:")
        print(formal_result)

    if test_result:
        #打印结果
        print("{")
        for i, (key, value) in enumerate(test_result.items()):
            if i < len(test_result) - 1:
                print(f'    "{key}":{value},')
            else:
                print(f'    "{key}":{value}')
        print("}")

        # 也可以直接使用result字典
        print(f"总共{i+1}列")
        print("\ntest可以直接使用的字典:")
        print(test_result)

    # 执行对比
    result = compare_excel_dicts(test_result, formal_result)

    # 打印结果
    print_comparison_result(result)